﻿using System;
using System.Data.SqlClient;
using System.Data;
using Grishko.DataTier;
using Grishko.DataTier.SqlLibrary;
using OutdoorLib.BaseEntity.Constraction;

namespace OutdoorLib.BaseEntity.Constraction
{

    public class SurfaceItemBox : SideLst.SideItemBox
    {
        public SurfaceItemBox() { }

        private Surface _Surface = null;
        public Surface Surface { get { return _Surface; } set { _Surface = value; } }
    }
    
    /// <summary>
    /// Список поверхностей с набором связанных контрактов позв. определить 
    /// состояние поверности 
    /// </summary>
    public class SurfaceItemBox_WithShedule : SurfaceItemBox
    {
        public SurfaceItemBox_WithShedule() { }

        private EntityList _Contracts = new EntityList();
        public EntityList Contracts { get { return _Contracts; } set { _Contracts = value; } }
    }



    /// <summary>
    /// Список возвращает xml соответсвующий SurfaceItemBox
    /// </summary>
    public class SurfaceList : SqlStatmentPropertyOf_PagingList
    {       
        public override void Load()
        {
            SecuretyContext SC = AppContext.GetSC();
            try
            {
                if (null == _SqlStatment) return;

                //_SqlStatment.PrepareStatment();
                string sql = _SqlStatment.GetStatment();

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                cmd.CommandText = sql;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection = SC.Connection;
                SC.Connection.Open();

                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                DataTable tbl = ds.Tables[1];
                FetchRecords(tbl); 
                tbl = ds.Tables[0];
                this.OutputRwCnt = Convert.ToInt16(tbl.Rows[0][0].ToString());
            }
            catch
            {
                return;
            }
            finally
            {
                SC.Connection.Close();
            }
        }
        protected  virtual void FetchRecords(DataTable tbl)
        {
            foreach (DataRow rw in tbl.Rows)
            {
                Side side = (Side)EntitySingle.GetEntity(rw[0].ToString());
                ConstractionEntity constr = (ConstractionEntity)EntitySingle.GetEntity(rw[1].ToString());
                Surface surface = (Surface)EntitySingle.GetEntity(rw[2].ToString());
                SurfaceItemBox i = new SurfaceItemBox() { Surface = surface, Constraction = constr, Side = side };
                this.Add(i);
            }
        }
    }
    public class SurfaceList_sqlLoadStatment : ListLoadStatment
    {
        public override string vPropertyOf_ViewName
        {
            get
            {
                return "v_srch_PropertyOfSurface";
            }
        }

        public override string Source_ViewName
        {
            get
            {
                return "v_Surface";
            }
        }



        //protected override string GetPageSql()
        //{
        //    return "";
        //}

        //protected override string GetResultSetSql()
        //{
        //    return String.Empty;

        //}

        //protected override string GetResultToXmlVarible()
        //{
        //    return " select #SelectList.Id as SurfaceID, v_Link_SideSurface.SideID as SideId,v_Link_ConstractionSide.ConstractionId as ConstractionId,v_Constraction.Xml.value('(/Constraction/@GID)[1]','nvarchar(50)') as Number ,v_surface.Xml.value('(/Constraction/@Number)[1]','nvarchar(50)') as SurfNumber into #SURFACEPAGE from v_Link_SideSurface join #SelectList on #SelectList.id=v_Link_SideSurface.SurfaceId join v_surface on v_surface.id=v_Link_SideSurface.surfaceId join v_Link_ConstractionSide on v_Link_ConstractionSide.SideID=v_Link_SideSurface.SideId	join v_Constraction on v_Constraction.ID=v_Link_ConstractionSide.ConstractionID select  #SURFACEPAGE.SurfaceID,	#SURFACEPAGE.SideID,	#SURFACEPAGE.ConstractionID,v_sides.Xml.value('(/Side/@Litera)[1]', 'nvarchar(50)') Litera,	#SURFACEPAGE.Number,#SURFACEPAGE.SurfNumber  into	#Page from 	#SURFACEPAGE join v_Surface on v_Surface.ID=#SURFACEPAGE.SurfaceID	join v_Sides on v_Sides.ID=#SURFACEPAGE.SideID	select count(*) from #Page select 	v_sides.xml as Side,	Constraction.xml as Constraction, 	v_surface.xml as Surface from 	(select #page.*,  ROW_NUMBER() OVER (ORDER BY #page.Number,#page.Litera,#page.SurfNumber) AS row_num  from #page) as Page join v_Constraction Constraction on Constraction.id=Page.ConstractionID 	join v_sides on v_sides.id=Page.SideID 	join v_Link_SideSurface on v_Link_SideSurface.SideID=v_sides.id	join v_surface on v_surface.id=v_Link_SideSurface.surfaceId where row_num between " + _StartRowIndex.ToString() + " and " + _EndRowIndex.ToString() + " ";
        //}

    }

    /// <summary>
    /// список поверхностей со связанными контрактами за период
    /// </summary>
    public class SurfaceList_WithShedule : SurfaceList
    {
        
        public override void Load()
        {
            base.Load();
        }
        protected override void FetchRecords(DataTable tbl)
        {
            foreach (DataRow rw in tbl.Rows)
            {
                Side side = (Side)EntitySingle.GetEntity(rw[0].ToString());
                ConstractionEntity constr = (ConstractionEntity)EntitySingle.GetEntity(rw[1].ToString());
                Surface surface = (Surface)EntitySingle.GetEntity(rw[2].ToString());
                SurfaceItemBox_WithShedule i = new SurfaceItemBox_WithShedule() { Surface = surface, Constraction = constr, Side = side };
                EntityList l = new EntityList();
                 if(!rw[3].ToString().Equals (string .Empty ) )l.ReadXml(rw[3].ToString());
                 i.Contracts = l;
                this.Add(i);
            }
        }
        
    }
    public class SurfaceList_WithShedule_sqlLoadStatment : SurfaceList_sqlLoadStatment
    {
        public SurfaceList_WithShedule_sqlLoadStatment() { }

        //protected override string GetResultToXmlVarible()
        //{
        //    string s = " Declare @dtStart as datetime Declare @dtFinish as datetime select @dtStart=cast('01.01.2008' as DateTime),@dtFinish=cast('01.01.2010' as DateTime) SELECT T.c.value('(./@ID)[1]','Varchar(max)') as SurfaceID, v_Contracts.xml,T.c.value('(./@DtStart)[1]','DateTime') as DtStart, T.c.value('(./@DtFinish)[1]','DateTime') as DtFinish into 	#ContractSource FROM   v_Contracts CROSS APPLY XML.nodes('*//ContractSurfaceItem') T(c) WHERE T.c.value('(./@DtStart)[1]','DateTime') between @dtStart and @dtFinish select #SurfaceBox.Side, #SurfaceBox.Constraction,	#SurfaceBox.Surface, (select [xml].query('.') from #ContractSource where #ContractSource.SurfaceID=#SurfaceBox.id FOR XML PATH(''), TYPE, ELEMENTS , ROOT('EntityList')) as LinkedContracts from #SurfaceBox ";
        //    return " select #SelectList.Id as SurfaceID, v_Link_SideSurface.SideID as SideId,v_Link_ConstractionSide.ConstractionId as ConstractionId,v_Constraction.Xml.value('(/Constraction/@GID)[1]','nvarchar(50)') as Number ,v_surface.Xml.value('(/Constraction/@Number)[1]','nvarchar(50)') as SurfNumber into #SURFACEPAGE from v_Link_SideSurface join #SelectList on #SelectList.id=v_Link_SideSurface.SurfaceId join v_surface on v_surface.id=v_Link_SideSurface.surfaceId join v_Link_ConstractionSide on v_Link_ConstractionSide.SideID=v_Link_SideSurface.SideId	join v_Constraction on v_Constraction.ID=v_Link_ConstractionSide.ConstractionID select  #SURFACEPAGE.SurfaceID,	#SURFACEPAGE.SideID,	#SURFACEPAGE.ConstractionID,v_sides.Xml.value('(/Side/@Litera)[1]', 'nvarchar(50)') Litera,	#SURFACEPAGE.Number,#SURFACEPAGE.SurfNumber  into	#Page from 	#SURFACEPAGE join v_Surface on v_Surface.ID=#SURFACEPAGE.SurfaceID	join v_Sides on v_Sides.ID=#SURFACEPAGE.SideID	select count(*) from #Page select 	v_sides.xml as Side,	Constraction.xml as Constraction, 	v_surface.xml as Surface,v_surface.id into #SurfaceBox from 	(select #page.*,  ROW_NUMBER() OVER (ORDER BY #page.Number,#page.Litera,#page.SurfNumber) AS row_num  from #page) as Page join v_Constraction Constraction on Constraction.id=Page.ConstractionID 	join v_sides on v_sides.id=Page.SideID 	join v_Link_SideSurface on v_Link_SideSurface.SideID=v_sides.id	join v_surface on v_surface.id=v_Link_SideSurface.surfaceId where row_num between " + _StartRowIndex.ToString() + " and " + _EndRowIndex.ToString() + s;
        //}
    }
}